const XLSX = require('xlsx'); // npm install xlsx
const { Readable } = require('stream');

module.exports = function (app, pool) {
    // 搜索图书
    app.get('/api/books/search', async (req, res) => {
        try {
            const searchTerm = req.query.q || '';
            if (!searchTerm) {
                // 搜索词为空时返回所有图书
                return app._router.handle(req, res, () => { }); // 复用获取所有图书的逻辑
            }

            // 模糊查询书名或作者
            const [rows] = await pool.execute(
                'SELECT * FROM books WHERE title LIKE ? OR author LIKE ? ORDER BY created_at DESC',
                [`%${searchTerm}%`, `%${searchTerm}%`]
            );
            // 淘宝 es  url  

            res.json(rows);
        } catch (error) {
            console.error('搜索图书失败:', error);
            res.status(500).json({ message: '服务器内部错误' });
        }
    });

    // 获取所有图书
    app.get('/api/books', async (req, res) => {
        try {
            const [rows] = await pool.execute('SELECT * FROM books ORDER BY created_at DESC');
            res.json(rows);
        } catch (error) {
            console.error('获取图书列表失败:', error);
            res.status(500).json({ message: '服务器内部错误' });
        }
    });

    // 获取单本图书（用于编辑功能）
    app.get('/api/books/:id', async (req, res) => {
        try {
            const [rows] = await pool.execute('SELECT * FROM books WHERE id = ?', [req.params.id]);

            if (rows.length === 0) {
                return res.status(404).json({ message: '图书不存在' });
            }

            res.json(rows[0]);
        } catch (error) {
            console.error('获取图书详情失败:', error);
            res.status(500).json({ message: '服务器内部错误' });
        }
    });
    
    // 添加新图书
    app.post('/api/books', async (req, res) => {
        try {
            const { title, author, description } = req.body;
            if (!title || !author) {
                return res.status(400).json({ message: '书名和作者是必填项' });
            }

            // 新增图书默认状态为"可借"
            const [result] = await pool.execute(
                `INSERT INTO books 
                 (title, author, description, status, created_at) 
                 VALUES (?, ?, ?, 'available', NOW())`,
                [title, author, description || '']
            );

            res.status(201).json({
                message: '添加成功',
                bookId: result.insertId
            });
        } catch (error) {
            console.error('添加图书失败:', error);
            res.status(500).json({ message: '服务器内部错误' });
        }
    });

    // 更新图书信息
    app.put('/api/books/:id', async (req, res) => {
        try {
            const { title, author, description, status } = req.body;
            // 验证必填项
            if (!title || !author) {
                return res.status(400).json({ message: '书名和作者是必填项' });
            }

            const [result] = await pool.execute(
                'UPDATE books SET title = ?, author = ?, description = ?, status = ? WHERE id = ?',
                [title, author, description || '', status || 'available', req.params.id]
            );

            if (result.affectedRows === 0) {
                return res.status(404).json({ message: '图书不存在' });
            }

            res.json({ message: '更新成功' });
        } catch (error) {
            console.error('更新图书失败:', error);
            res.status(500).json({ message: '服务器内部错误' });
        }
    });

    // 借阅图书
    app.put('/api/books/:id/borrow', async (req, res) => {
        try {
            // 先检查图书是否存在
            const [bookRows] = await pool.execute(
                'SELECT * FROM books WHERE id = ?',
                [req.params.id]
            );

            if (bookRows.length === 0) {
                return res.status(404).json({ message: '图书不存在' });
            }

            const book = bookRows[0];
            if (book.status !== 'available') {
                return res.status(400).json({ message: '该图书已被借出' });
            }

            // 更新图书状态
            await pool.execute(
                'UPDATE books SET status = ? WHERE id = ?',
                ['checked_out', req.params.id]
            );

            res.json({ message: '借阅成功' });
        } catch (error) {
            console.error('借阅失败:', error);
            res.status(500).json({ message: '服务器内部错误' });
        }
    });

    // 归还图书
    app.put('/api/books/:id/return', async (req, res) => {
        try {
            // 先检查图书是否存在
            const [bookRows] = await pool.execute(
                'SELECT * FROM books WHERE id = ?',
                [req.params.id]
            );

            if (bookRows.length === 0) {
                return res.status(404).json({ message: '图书不存在' });
            }

            const book = bookRows[0];
            if (book.status !== 'checked_out') {
                return res.status(400).json({ message: '该图书未被借出' });
            }

            // 更新图书状态
            await pool.execute(
                'UPDATE books SET status = ? WHERE id = ?',
                ['available', req.params.id]
            );

            res.json({ message: '归还成功' });
        } catch (error) {
            console.error('归还失败:', error);
            res.status(500).json({ message: '服务器内部错误' });
        }
    });

    // 删除图书
    app.delete('/api/books/:id', async (req, res) => {
        try {
            const [result] = await pool.execute('DELETE FROM books WHERE id = ?', [req.params.id]);

            if (result.affectedRows === 0) {
                return res.status(404).json({ message: '图书不存在' });
            }

            res.json({ message: '删除成功' });
        } catch (error) {
            console.error('删除图书失败:', error);
            res.status(500).json({ message: '服务器内部错误' });
        }
    });

    // 导出图书列表为Excel
    app.get('/api/books/export', async (req, res) => {
        try {
            const [rows] = await pool.execute('SELECT id, title, author, status, created_at FROM books ORDER BY created_at DESC');

            // 转换数据格式（中文状态名）
            const exportData = rows.map(book => ({
                ID: book.id,
                书名: book.title,
                作者: book.author,
                状态: book.status === 'available' ? '可借' : '已借出',
                创建时间: book.created_at ? new Date(book.created_at).toLocaleString() : ''
            }));

            // 创建工作簿和工作表
            const workbook = XLSX.utils.book_new();
            const worksheet = XLSX.utils.json_to_sheet(exportData);

            // 添加工作表到工作簿
            XLSX.utils.book_append_sheet(workbook, worksheet, '图书列表');

            // 生成Excel文件流
            const excelBuffer = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer' });
            const stream = Readable.from(excelBuffer);

            // 设置响应头
            res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            res.setHeader('Content-Disposition', `attachment; filename="图书列表_${new Date().toLocaleDateString()}.xlsx"`);

            // 发送文件流
            stream.pipe(res);
        } catch (error) {
            console.error('导出图书失败:', error);
            res.status(500).json({ message: '导出失败，请重试' });
        }
    });
};